<?php


namespace App\Exports;


use App\Models\PartCompany;
use App\Models\ProjectClassify;
use App\Models\SortLink;
use App\Services\BaseService;
use App\Services\CommonService;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithColumnWidths;
use Maatwebsite\Excel\Concerns\WithCustomStartCell;
use Maatwebsite\Excel\Concerns\WithStyles;
use Maatwebsite\Excel\Concerns\WithTitle;
use PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

/**
 * Class BookTitleExport29
 * @package App\Exports
 * 29号案卷目录导出格式
 */
class ProjectBookTitleExportLY implements FromCollection, WithTitle, WithColumnWidths, WithStyles, WithCustomStartCell
{
    use Exportable;

    protected $totalRow; //总行数
    protected $projectId; //参见单位
    protected $partCompanyId; //参见单位id(选填)，如果存在，则代表导出指定参见单位下的全部案卷
    protected $projectClassifyId; //单位工程id，如果存在，则代表导出指定单位工程下的全部案卷
    protected $sheetName;
    protected $parentNodeId;

    public function __construct($sheetName, $projectId, $partCompanyId = 0, $parentNodeId = 0)
    {
        $this->sheetName     = $sheetName;
        $this->projectId     = $projectId;
        $this->partCompanyId = $partCompanyId;
        $this->parentNodeId  = $parentNodeId; //一个有下级的单位工程ID
    }

    /**
     * 获取数据集合
     */
    public function collection()
    {
        $resData            = CommonService::getIdsByProjectId($this->projectId, $this->partCompanyId);
        $partCompanyIds     = $resData['part_company_ids'];
        $projectClassifyIds = $resData['project_classify_ids'];

        //下面这一些是导出一个有下级的单位工程用的，把范围限定在下级单位工程
        $redisKey = "childrenBts:{$this->partCompanyId}:{$this->parentNodeId}";
        $tables = redis()->smembers($redisKey); //获取当前参建单位下的案卷全部的分表
        $childrenProClassifyIds = [];
        foreach ($tables as $table) {
            $ids = CommonService::getTableParams($table);
            $childrenProClassifyIds[] = $ids['id2'];
        }

        $number = 1;
        $r_list = [];
        foreach ($partCompanyIds as $partCompanyId) {
            $partCompanyName = self::getPartCompanyName($partCompanyId);
            /**
             * 导出参建单位案卷，注意把单位工程按照顺序从上到下排好,如果有排序数据，要优先使用排序数据，
             * 排序数据会在  ProjectClassifyController 的 treeList方法里面生成
             */
            $projectClassifyIdsSL = SortLink::query()
                ->when($childrenProClassifyIds, function ($query) use ($childrenProClassifyIds) {
                    $query->whereIn('project_classify_id', $childrenProClassifyIds);
                })
                ->where('part_company_id', $partCompanyId)
                ->orderBy('id')
                ->pluck('project_classify_id');

            if ($projectClassifyIdsSL->isNotEmpty()) {
                $projectClassifyIds = $projectClassifyIdsSL->toArray();
            }
            foreach ($projectClassifyIds as $projectClassifyId) {
                $table = "book_titles_{$partCompanyId}_{$projectClassifyId}";
                if ($projectClassifyId && Schema::hasTable($table)) { //如果有表
                    $count = DB::table($table)->whereNull('deleted_at')->count('id');
                    if (!empty($count)) { //如果表有数据
                        $bookTitleInstance = CommonService::getBookTitleSearchInstance($partCompanyId, $projectClassifyId);
                        $list = $bookTitleInstance
                            ->where('part_company_id', $partCompanyId)
                            ->where('project_classify_id', $projectClassifyId)
                            ->select('id', 'archives_no', 'title', 'word_material', 'blueprint', 'page_num', 'store_period', 'secret_level', 'start_date', 'end_date', 'volume', 'remark')
                            ->orderBy('serial_no')
                            ->get();
                        if ($list->isNotEmpty()) {
                            $projectClassifyName = self::getProjectClassifyName($projectClassifyId);
                            $list    = $list->toArray();
                            $r_list  = [];
                            foreach ($list as $key => $val) {
                                $arr['number']           = $number++;             //序号
                                $arr['archives_no']      = str_replace('•', '·', $val['archives_no']);   //档案号
                                $arr['title']            = htmlspecialchars_decode($val['title']); //标题
                                $arr['word_material']    = $val['word_material']; //文字
                                $arr['blueprint']        = $val['blueprint'] ?: '/'; //蓝图
                                $arr['total_page']       = $val['page_num']; //总张数
                                $arr['part_company']     = $partCompanyName; //形成单位
                                $arr['start_end_date']   = format_date($val['start_date'], $val['end_date']); //起止日期
                                $arr['store_period']     = $val['store_period'];  //保管期限
                                $arr['secret_level']     = ''; //密级
                                $arr['volume']           = ''; //册数
                                $arr['box_size']         = ''; //盒子尺寸
                                $arr['store_place']      = ''; //存放位置
                                $arr['remark']           = ''; //备注
                                $arr['data_count']       = CommonService::getBookCatalogInstance($this->partCompanyId, $projectClassifyId)->where('book_title_id', $val['id'])->count() ?: 0;
                                $arr['project_classify'] = $projectClassifyName;
                                $r_list[]                = $arr;
                            }
                        }
                    }
                }

            }
        }
        $this->totalRow = count($r_list) + 5;

        return collect($r_list);
    }

    /**
     * 获取单位工程的名字
     */
    public static function getProjectClassifyName($id)
    {
        $redisKey = 'ProjectClassifyId:' . $id;
        $name     = redis()->get($redisKey);
        if (empty($name)) {
            $name = ProjectClassify::query()->where('id', $id)->value('title');
            redis()->setex($redisKey, 600, $name);
        }
        return $name;
    }

    /**
     * 获取参建单位名字
     */
    public static function getPartCompanyName($id)
    {
        $redisKey = 'PartCompanyId:' . $id;
        $name     = redis()->get($redisKey);
        if (empty($name)) {
            $name = PartCompany::query()->where('id', $id)->value('title');
            redis()->setex($redisKey, 600, $name);
        }
        return $name;
    }

    /**
     * sheet名称
     * @return string
     */
    public function title(): string
    {
        return $this->sheetName . '全部案卷';
    }

    /**
     * 设置列宽
     * @return array
     */
    public function columnWidths(): array
    {
        return [
            'A' => 10, 'B' => 20, 'C' => 60, 'D' => 6, 'E' => 10, 'F' => 9, 'G' => 12, 'H' => 12, 'I' => 12, 'J' => 6, 'K' => 6, 'L' => 6, 'M' => 6, 'N' => 6, 'O' => 6, 'P' => 10
        ];
    }

    /**
     * 从哪个单元格开始渲染数据，数据集合如 $list
     * @return string
     */
    public function startCell(): string
    {
        return 'A6';
    }

    /**
     * 样式设置
     * @param Worksheet $sheet
     * @throws PhpSpreadsheetException
     */
    public function styles(Worksheet $sheet)
    {
        $sheet->mergeCells('A1:P1');
        $sheet->setCellValue('A1', '科  技  档  案  案  卷  目  录');

        $sheet->mergeCells('A2:C2');
        $sheet->setCellValue('A2', '建设项目（简称）：');

        $sheet->mergeCells('D2:G2');
        $sheet->setCellValue('D2', '专业：');

        $sheet->mergeCells('H2:N2');
        $sheet->setCellValue('H2', '里程段：');

        $sheet->mergeCells('A3:A5'); //序号
        $sheet->mergeCells('B3:B5'); //档案号
        $sheet->mergeCells('C3:C5'); //案卷题名
        $sheet->mergeCells('D3:F3'); //案卷页数
        $sheet->mergeCells('D4:D5'); //文字
        $sheet->mergeCells('E4:E5'); //蓝图
        $sheet->mergeCells('F4:F5'); //总张数
        $sheet->mergeCells('G3:G5'); //形成单位
        $sheet->mergeCells('H3:H5'); //形成时间
        $sheet->mergeCells('I3:I5'); //保管期限
        $sheet->mergeCells('J3:J5'); //密级
        $sheet->mergeCells('K3:K5'); //册数
        $sheet->mergeCells('L3:L5'); //盒子尺寸
        $sheet->mergeCells('M3:M5'); //存放位置
        $sheet->mergeCells('N3:N5'); //备注
        $sheet->mergeCells('O3:O5'); //卷内数量
        $sheet->mergeCells('P3:P5'); //卷内数量
        /**
         * 属性名设置
         */
        $sheet->setCellValue('A3', '序号');
        $sheet->setCellValue('B3', '档案号');
        $sheet->setCellValue('C3', '案卷名称');
        $sheet->setCellValue('D3', '张数');
        $sheet->setCellValue('D4', '文字');
        $sheet->setCellValue('E4', '蓝图');
        $sheet->setCellValue('F4', '总张数');
        $sheet->setCellValue('G3', '形成单位');
        $sheet->setCellValue('H3', '形成时间');
        $sheet->setCellValue('I3', '保管期限');
        $sheet->setCellValue('J3', '密级');
        $sheet->setCellValue('K3', '册数');
        $sheet->setCellValue('L3', '盒子尺寸');
        $sheet->setCellValue('M3', '存放位置');
        $sheet->setCellValue('N3', '备注');
        $sheet->setCellValue('O3', '卷内数量');
        $sheet->setCellValue('P3', '单位工程');
        /**
         * 顶部案卷目录样式设置
         */
        $sheet->getStyle('A1')->getFont()->setSize(24); //大小
        $sheet->getStyle('A1')->applyFromArray([
            'font'      => [
                'size' => 24,
                'name' => '宋体',
                'bold' => true,
            ],
            'alignment' => [
                'vertical' => 'left',
            ],
        ]);
        $sheet->getStyle('A1:P' . $this->totalRow)->getAlignment()->setVertical('center');//垂直居中
        $sheet->getStyle('A1:P' . $this->totalRow)->getAlignment()->setHorizontal('center');//水平居中
        $sheet->getStyle('A1:P' . $this->totalRow)->getAlignment()->setWrapText(true); //自动换行
        $sheet->getStyle('A2:P2')->applyFromArray([
            'font'      => [
                'size' => 11,
                'name' => '宋体',
            ],
            'alignment' => [
                'vertical'   => 'center',
                'horizontal' => 'left', //水平靠左
            ],
        ]);
        $sheet->getStyle('A3:P3')->applyFromArray([
            'font' => [
                'size' => 10,
                'name' => '宋体',
            ],
        ]);
        $sheet->getStyle('A6:P' . $this->totalRow)->applyFromArray([
            'font' => [
                'size' => 9,
                'name' => '仿宋_GB2312',
            ],
        ]);
        $sheet->getStyle('C6:C' . $this->totalRow)->applyFromArray([
            'alignment' => [
                'horizontal' => 'left', //水平靠左
            ],
        ]);
        for ($row = 1; $row <= $this->totalRow; $row++) { //设置行高
            if ($row == 3 || $row == 4 || $row == 5) {
                $sheet->getRowDimension($row)->setRowHeight(15);
            } else {
                $sheet->getRowDimension($row)->setRowHeight(42);
            }
        }
        /**
         * 设置边框
         */
        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN //细边框
                ]
            ]
        ];
        $sheet->getStyle('A3:P' . $this->totalRow)->applyFromArray($styleArray);
    }

}
